﻿#include "databaseinterface.h"
#include "globalsymbol.h"

#include <QDebug>
#include <QDateTime>
#include <QElapsedTimer>

DatabaseInterface::DatabaseInterface(QSqlDatabase *sqlDatabase, QObject *parent) : QObject(parent), m_sqlDatabase(sqlDatabase)
{
}

DatabaseInterface::~DatabaseInterface()
{
}

bool DatabaseInterface::vacuumSqlite()
{
	// 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare("VACUUM");

    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    return true;
}

bool DatabaseInterface::getChannelDescriptionList(QList<ChannelDescription> &channelDescriptionList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare("SELECT ID, NAME, DATATYPE, SIMUTYPE, COEFFICIENT, INITVALUE, INCREASESTEP, MINVALUE, MAXVALUE, HEX, FREEZED FROM T_CHANNEL_DESCRIPTION");
    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    // 填充数据列表
    channelDescriptionList.clear();
    while(query.next()) {
        ChannelDescription channelDescription;
        channelDescription.id = query.value(0).toString();
        channelDescription.name = query.value(1).toString();
        channelDescription.dataType = query.value(2).toString();
        channelDescription.simuType = query.value(3).toString();
        channelDescription.coefficient = query.value(4).toDouble();
        channelDescription.initValue = query.value(5).toInt();
        channelDescription.increaseStep = query.value(6).toInt();
        channelDescription.minValue = query.value(7).toDouble();
        channelDescription.maxValue = query.value(8).toDouble();
        channelDescription.hex = query.value(9).toBool();
        channelDescription.freezed = query.value(10).toBool();

        channelDescriptionList << channelDescription;
    }

    return true;
}

bool DatabaseInterface::getChannelCoefficientList(QList<double> &coefficientList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare("SELECT COEFFICIENT FROM T_CHANNEL_DESCRIPTION");
    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    // 填充数据列表
    coefficientList.clear();
    while(query.next()) {
        coefficientList << query.value(0).toDouble();
    }

    return true;
}

bool DatabaseInterface::updateChannelCoefficient(const QList<double> &coefficientList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    // 开启事务
    if (m_sqlDatabase->transaction()) {
        for (int i = 0; i < coefficientList.length(); i++) {
            QString id = QString("%1").arg(i, 3, 10, QLatin1Char('0'));
            query.prepare("UPDATE T_CHANNEL_DESCRIPTION SET COEFFICIENT=:coefficient WHERE ID=:id");
            query.bindValue(":coefficient", coefficientList.at(i));
            query.bindValue(":id", id);
            if (!query.exec()) {
                //回滚事务
                m_sqlDatabase->rollback();
                return false;
            }
        }

        // 提交事务
        if (m_sqlDatabase->commit())
            return true;
    } else {
        qDebug() << "connection start transaction failed.";
    }

    return true;
}

bool DatabaseInterface::deleteChannelValue()
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    // 开启事务
    if (m_sqlDatabase->transaction()) {
        query.prepare("DELETE FROM T_CHANNEL_VALUE");
        bool success = query.exec();
        if (!success) {
            //回滚事务
            m_sqlDatabase->rollback();
            return false;
        }

        query.prepare("UPDATE sqlite_sequence SET seq=0 WHERE name='T_CHANNEL_VALUE'");
        success = query.exec();
        if (!success) {
            //回滚事务
            m_sqlDatabase->rollback();
            return false;
        }

        // 提交事务
        if (m_sqlDatabase->commit())
            return true;
    } else {
        qDebug() << "connection start transaction failed.";
    }

    return true;
}

bool DatabaseInterface::insertChannelValue(const QList<ChannelValue> &channelValueList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery transaction(*m_sqlDatabase);

    try {
        // 拼接数据插入字符串
        QString insertString = "INSERT INTO T_CHANNEL_VALUE( TIMESTAMP";
        for (int i = 0; i < CHANNEL_COUNT; i++)
            insertString.append(QString(", CHANNEL%1").arg(i, 3, 10, QLatin1Char('0')));
        insertString.append(") VALUES( :timestamp");
        for (int i = 0; i < CHANNEL_COUNT; i++)
            insertString.append(QString(", :value%1").arg(i));
        insertString.append(" )");
        //qDebug() << "*********" << insertString;

        // 批量插入计时
        //QElapsedTimer timer;
        //timer.start();

        // 开启事务，BEGIN
        if (m_sqlDatabase->transaction()) {
            for (int i = 0; i < channelValueList.count(); i++) {
                transaction.prepare(insertString);
                transaction.bindValue(":timestamp", channelValueList.at(i).timeStamp);
                for (int j = 0; j < CHANNEL_COUNT; j++)
                    transaction.bindValue(QString(":value%1").arg(j), channelValueList.at(i).valueList.at(j));

                // 插入错误
                if (!transaction.exec()) {
                    // 回滚事务
                    m_sqlDatabase->rollback();
                    return false;
                }
            }
            //qDebug() << "transaction ( insertChannelValue ) commit time(ms): " << timer.elapsed();

            // 提交事务
            if (m_sqlDatabase->commit())
                return true;
        } else {
            qDebug() << "connection start transaction failed.";
        }
    } catch (QException) {
        // 回滚事务
        m_sqlDatabase->rollback();
        return false;
    }

    return false;
}

bool DatabaseInterface::getChannelValueList(const QStringList &idList, QList<ChannelValue> &channelValueList)
{
    // 拼接数据查询字符串
    QString queryString = "SELECT TIMESTAMP";
    for (int i = 0; i < idList.size(); i++)
        queryString.append(QString(", CHANNEL%1").arg(idList.at(i)));
    queryString.append(" FROM T_CHANNEL_VALUE");
    //qDebug() << "*********" << queryString;

    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare(queryString);
    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    // 填充数据列表
    channelValueList.clear();
    while (query.next()) {
        ChannelValue channelValue;
        channelValue.timeStamp = query.value(0).toLongLong();
        for (int i = 0; i < idList.size(); i++)
            channelValue.valueList << query.value(i + 1).toDouble();

        channelValueList << channelValue;
    }

    return true;
}

bool DatabaseInterface::getReportLineList(QList<ReportLine> &reportLineList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare("SELECT ID, NAME, MINVALUE1, MAXVALUE1, MINVALUE2, MAXVALUE2 FROM T_REPORT_LINE");
    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    // 填充数据列表
    reportLineList.clear();
    while(query.next()) {
        ReportLine reportLine;
        reportLine.id = query.value(0).toInt();
        reportLine.name = query.value(1).toString();
        reportLine.minValue1 = query.value(2).toDouble();
        reportLine.maxValue1 = query.value(3).toDouble();
        reportLine.minValue2 = query.value(4).toDouble();
        reportLine.maxValue2 = query.value(5).toDouble();

        reportLineList << reportLine;
    }

    return true;
}

bool DatabaseInterface::getCmdList(QList<Cmd> &cmdList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare("SELECT ID, NAME, CMD, DESCRIPTION, REMARK FROM T_RS485_CMD");
    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    // 填充数据列表
    cmdList.clear();
    while(query.next()) {
        Cmd cmd;
        cmd.id = query.value(0).toInt();
        cmd.name = query.value(1).toString();
        cmd.cmd = query.value(2).toString();
        cmd.description = query.value(3).toString();
        cmd.remark = query.value(4).toString();

        cmdList << cmd;
    }

    return true;
}

bool DatabaseInterface::getTrainStepList(QList<TrainStep> &trainStepList)
{
    // 用于执行sql语句的对象; 跨线程访问需要QSqlQuery query (QSqlDatabase  db) 的方式初始化绑定
    QSqlQuery query(*m_sqlDatabase);
    query.prepare("SELECT ID, STEPCODE, STEPNAME, LEVEL, NUMBER, CATEGORYCODE, CATEGORYNAME, FAULTCODE, FAULTNAME, REMARK FROM T_TRAIN_STEP");
    if (!query.exec()) {
        qDebug() << "query error: " << query.lastError();
        return false;
    }

    // 填充数据列表
    trainStepList.clear();
    while(query.next()) {
        TrainStep trainStep;
        trainStep.id = query.value(0).toInt();
        trainStep.stepCode = query.value(1).toString();
        trainStep.stepName = query.value(2).toString();
        trainStep.level = query.value(3).toInt();
        trainStep.number = query.value(4).toInt();
        trainStep.categoryCode = query.value(5).toString();
        trainStep.categoryName = query.value(6).toString();
        trainStep.faultCode = query.value(7).toString();
        trainStep.faultName = query.value(8).toString();
        trainStep.remark = query.value(9).toString();

        trainStepList << trainStep;
    }

    return true;
}






